<?php
#
# dmBridge: a data access framework for CONTENTdm(R)
#
# Copyright © 2009, 2010, 2011 Board of Regents of the Nevada System of Higher
# Education, on behalf of the University of Nevada, Las Vegas
#

/**
 * An instance of this class will be returned by
 * DMDataStoreFactory::getDataStore() when PDO is the current data store.
 * This class contains a lot of accessors that can be safely inherited by
 * most subclasses because they use database-agnostic SQL. Concrete instances
 * of this class should feel free to override whatever ones they need to.
 *
 * @author Alex Dolski <alex.dolski@unlv.edu>
 * @license http://www.opensource.org/licenses/mit-license.php
 */
abstract class DMPDODataStore {

	protected static $tables = array("comment", "rating", "tag");

	private $tables_exist;


	protected function __construct() {}
	public function __clone() {}
	public function __wakeup() {}


	private function allTablesExist() {
		if ($this->tables_exist === null) {
			foreach (self::$tables as $t) {
				if (!$this->tableExists($t)) {
					return false;
				}
			}
		}
		$this->tables_exist = true;
		return true;
	}

	/**
	 * @throws DMPDOException
	 * @return Boolean
	 * @since 0.3
	 */
	public function isAvailable() {
		if (!$this->getConnection()) {
			return false;
		}
		if (!$this->allTablesExist()) {
			if (!$this->createtables()) {
				return false;
			}
		}
		return true;
	}

	/**
	 * Begins a new transaction.
	 */
	public function beginTransaction() {
		$this->getConnection()->beginTransaction();
	}

	/**
	 * Ends a transaction, committing all changes.
	 */
	public function commit() {
		$this->getConnection()->commit();
	}

	/**
	 * @throws DMPDOException
	 * @return void
	 */
	public function createTables() {
		if ($this->allTablesExist()) {
			return;
		}

		$sql = file_get_contents(
			sprintf("%s/../../includes/database/%s.sql",
				dirname(__FILE__),
				str_replace("pdo_", "", $this->driver)));
		$tmp = explode("-- ||| --", $sql);
		array_shift($tmp);
		foreach ($tmp as $cmd) {
			$stmt = $this->getConnection()->prepare(trim($cmd));
			$stmt->execute();
		}
	}

	/**
	 * @param string name
	 */
	public final function setDBName($name) {
		unset($this->dbcon);
		$this->dbname = $name;
	}

	/**
	 * @param string sql
	 * @param array params Array of bound parameters, e.g. ':key => value'
	 * @return Result of PDOStatement->fetchAll()
	 * @throws DMPDOException
	 */
	public function read($sql, array $params) {
		try {
			$stmt = $this->getConnection()->prepare($sql);
			$stmt->execute($params);
			return $stmt->fetchAll(PDO::FETCH_ASSOC);
		} catch (PDOException $e) {
			$e = new DMPDOException($e);
			throw $e;
		}
	}

	/**
	 * Rolls back all changes to the data store since the transaction was begun.
	 */
	public function rollBack() {
		$this->getConnection()->rollBack();
	}

	/**
	 * @return Boolean
	 */
	public function commentTableExists() {
		return $this->tableExists('comment');
	}

	/**
	 * @return Boolean
	 */
	public function ratingTableExists() {
		return $this->tableExists('rating');
	}

	/**
	 * @return Boolean
	 */
	public function tagTableExists() {
		return $this->tableExists('tag');
	}

	/**
	 * @param string name Table name
	 * @return Boolean
	 */
	private function tableExists($name) {
		/* There is no database-independent way to check if a table exists; so
		  we'll issue a select query and see if it fails. */
		try {
			$sql = sprintf("SELECT * FROM %s LIMIT 1", $name);
			$stmt = $this->getConnection()->prepare($sql);
			$stmt->execute();
			return true;
		} catch (PDOException $e) {
			$this->tables_exist = false;
			return false;
		}
	}

	/**
	 * @param string sql
	 * @param array params Array of bound parameters, in :param => value format
	 * @return int Number of affected rows
	 * @throws DMPDOException
	 * @since 0.3
	 */
	public function write($sql, array $params) {
		try {
			$stmt = $this->getConnection()->prepare($sql);
			$stmt->execute($params);
			return $stmt->rowCount();
		} catch (PDOException $e) {
			$e = new DMPDOException($e);
			throw $e;
		}
	}

	/**************************** COMMENTS *********************************/

	/**
	 * @param DMCommentQuery query
	 * @param bool count Whether to get the result count (true) or just the
	 * results
	 * @return Array of DMComment objects, or int, depending on the query
	 */
	public function getCommentsForQuery(DMCommentQuery $query, $count) {
		$select = "SELECT * ";
		if ($count) {
			$select = "SELECT COUNT(id) AS count ";
		}
		$from = "FROM comment ";

		$params = $where = $tmp = array();
		$i = 0;
		if (count($query->getObjects())) {
			foreach ($query->getObjects() as $obj) { // object
				$tmp[] = sprintf("(alias = :alias%d AND ptr = :ptr%d)", $i, $i);
				$params[':alias' . $i] = $obj->getCollection()->getAlias();
				$params[':ptr' . $i] = $obj->getPtr();
				$i++;
			}
		} else { // collections
			$skip_collections = false;
			foreach ($query->getCollections() as $col) {
				if ($col->getAlias() == "/dmdefault") {
					$skip_collections = true;
					break;
				}
			}
			if (!$skip_collections) {
				foreach ($query->getCollections() as $col) {
					$tmp[] = sprintf("alias = :alias%d", $i);
					$params[':alias' . $i] = $col->getAlias();
					$i++;
				}
			}
		}
		if (count($tmp)) {
			$where[] = implode(" OR ", $tmp);
		}

		// approved
		if ($query->getApproved() == -1) {
			$where[] = "is_approved < 1";
		} else if ($query->getApproved() == 1) {
			$where[] = "is_approved > 0";
		}
		
		// terms
		$i = 0;
		$tmp = array();
		foreach ($query->getPredicates() as $pred) {
			$tmp[] = sprintf("text LIKE :term%d", $pred->getField(), $i);
			$params[':term' . $i] = "%" . $pred->getString() . "%";
			$i++;
		}
		if (count($tmp)) {
			$where[] = implode(" AND ", $tmp);
		}

		$order = $limit = $offset = "";
		if (!$count) {
			// order
			if (count($query->getSortFields())) {
				$order = " ORDER BY";
				$orders = array();
				foreach ($query->getSortFields() as $field => $direction) {
					$orders[] = sprintf(" %s %s ", $field, $direction);
				}
				$order .= implode(", ", $orders);
			}
		
			// limit/offset
			$limit = sprintf(" LIMIT %d ", $query->getNumResultsPerPage());
			$offset = sprintf(" OFFSET %d ", $query->getStart());
		}

		$sql = $select . $from . "WHERE " . implode(" AND ", $where)
				. $order . $limit . $offset;
		$result = $this->read($sql, $params);

		if ($count) {
			return $result[0]['count'];
		} else {
			$comments = array();
			foreach ($result as $c) {
				try {
					$comments[] = $this->loadComment($c['id']);
				} catch (DMUnavailableModelException $e) {
					// comment is associated with an invalid object; skip it
				}
			}
			return $comments;
		}
	}

	/**
	 * @param DMObject obj
	 * @param int page
	 * @param int rpp Results per page
	 * @param Boolean|null sort True for ascending, false for descending, null
	 * for no sort
	 * @return array Array of DMComment objects
	 */
	public final function getApprovedCommentsForObject(
			DMObject $obj, $page, $rpp, $sort = true) {
		$sql = "SELECT id FROM comment
			WHERE alias = :alias AND ptr = :ptr AND is_approved > 0";
		if (!is_null($sort)) {
			$sql .= " ORDER BY posted_at";
			if ($sort === false) {
				$sql .= " DESC";
			}
		}
		$sql .= sprintf(" LIMIT %d OFFSET %d",
				$rpp,
				($page - 1) * $rpp);
		$params = array(
			':alias' => $obj->getCollection()->getAlias(),
			':ptr' => $obj->getPtr()
		);
		$comments = array();
		foreach ($this->read($sql, $params) as $c) {
			$comments[] = $this->loadComment($c['id']);
		}
		return $comments;
	}

	/**
	 * @param DMComment comment
	 * @return Boolean
	 */
	public function deleteComment(DMComment $comment) {
		$sql = 'DELETE FROM comment WHERE id = :id';
		$params = array(':id' => $comment->getID());
		return $this->write($sql, $params);
	}

	/**
	 * @param int id The ID of the comment to load
	 * @return DMComment
	 * @throws DMPDOException
	 * @throws DMUnavailableModelException
	 * @since 0.3
	 */
	public final function loadComment($id) {
		$sql = 'SELECT * FROM comment WHERE id = :id';
		$params = array(':id' => $id);
		$r = $this->read($sql, $params);
		if (count($r)) {
			$comment = new DMComment();
			$comment->setID($id);
			try {
				$comment->setEmail($r[0]['email']);
			} catch (DMIllegalArgumentException $e) {}
			$comment->setName($r[0]['name']);
			$comment->setValue($r[0]['text']);
			$comment->setApproved($r[0]['is_approved']);
			$comment->setTimestamp(new DMDateTime($r[0]['posted_at']));

			$collection = DMCollectionFactory::getCollection($r[0]['alias']);
			if ($collection) {
				$object = DMObjectFactory::getObject($collection, $r[0]['ptr']);
				$comment->setObject($object);
				return $comment;
			}
			return null;
		} else {
			throw new DMUnavailableModelException(
				DMLocalizedString::getString("INVALID_COMMENT"));
		}
	}

	/**
	 * @param DMComment comment
	 * @return Boolean
	 * @throws DMPDOException
	 * @since 0.3
	 */
	public final function saveComment(DMComment $comment) {
		$sql = "UPDATE comment SET email = :email, text = :text,
				name = :name, is_approved = :is_approved
			WHERE id = :id";
		$params = array(
			':email' => $comment->getEmail(),
			':text' => $comment->getValue(),
			':name' => $comment->getName(),
			':is_approved' => (int) $comment->isApproved(),
			':id' => $comment->getID()
		);
		return $this->write($sql, $params);
	}

	/**
	 * @param DMCollection col
	 * @return float
	 */
	public final function getMeanNumCommentsPerObjectInCollection(
			DMCollection $col) {
		$num_objects = $col->getNumObjects();
		if ($num_objects > 0) {
			$cq = new DMCommentQuery($this);
			$cq->addCollection($col);
			$cq->setApproved(1);
			$cq->getSearchResults();
			return $cq->getNumResults() / $num_objects;
		}
		return (float) 0;
	}

	/**
	 * @param DMCollection col
	 * @return int
	 */
	public final function getMedianNumCommentsPerObjectInCollection(
			DMCollection $col) {
		$sql = "SELECT COUNT(id) AS total
			FROM comment
			WHERE alias = :alias
			GROUP BY ptr
			ORDER BY total";
		$params = array(
			'alias' => $col->getAlias()
		);
		$result = $this->read($sql, $params);

		$counts = array();
		$num_counts = $col->getNumObjects() - count($result);
		for ($i = 0; $i < $num_counts; $i++) {
			$counts[] = 0;
		}
		foreach ($result as $row) {
			$counts[] = $row['total'];
		}
		if ($num_counts) {
			$middle = round($num_counts / 2);
			return ($middle - 1 > 0) ? $counts[$middle - 1] : 0;
		}
		return 0;
	}

	/**
	 * @return int
	 */
	public function getNumObjectsWithApprovedComments() {
		$sql = 'SELECT COUNT(alias) AS count FROM (
				SELECT DISTINCT alias, ptr
				FROM comment
				WHERE is_approved > 0
			) AS count';
		$result = $this->read($sql, array());
		return (int) $result[0]['count'];
	}

	/**
	 * @param DMObject obj
	 * @param DMComment comment
	 * @return Boolean
	 */
	public final function addObjectComment(DMObject $obj, DMComment $comment) {
		// check for duplicates, return true without saving if found
		$sql = "SELECT name, email, text
			FROM comment WHERE ptr = :ptr AND alias = :alias";
		$params = array(
			':ptr' => $obj->getPtr(),
			':alias' => $obj->getCollection()->getAlias()
		);
		foreach ($this->read($sql, $params) as $r) {
			if ($r['name'] == $comment->getName()
					&& $r['email'] == $comment->getEmail()
					&& $r['text'] == $comment->getValue()) {
				return true;
			}
		}
		// apparently no duplicates, so insert
		$params = array(
			':name' => $comment->getName(),
			':email' => $comment->getEmail(),
			':text' => $comment->getValue(),
			':ptr' => $obj->getPtr(),
			':alias' => $obj->getCollection()->getAlias(),
			':is_approved' => (int) $comment->isApproved(),
			':posted_at' => date('Y-m-d H:i:s', time())
		);
		if ($comment->getID()) {
			$sql = "INSERT INTO comment(id, name, email, text, ptr, alias,
					is_approved, posted_at)
				VALUES(:id, :name, :email, :text, :ptr, :alias, :is_approved,
					:posted_at)";
			$params[':id'] = $comment->getID();
		} else {
			$sql = "INSERT INTO comment(name, email, text, ptr, alias,
					is_approved, posted_at)
				VALUES(:name, :email, :text, :ptr, :alias, :is_approved,
					:posted_at)";
		}
		$this->write($sql, $params);

		// get last-inserted id in a database-independent way
		$sql = "SELECT MAX(id) AS id FROM comment";
		$result = $this->read($sql, array());
		$comment->setID($result[0]['id']);
		return true;
	}

	/**
	 * @param limit integer
	 * @return array of DMObjects, or an empty array if no comments exist for
	 * any object
	 */
	public function getObjectsWithMostComments($limit) {
		$sql = sprintf('SELECT COUNT(id) AS count, alias, ptr
			FROM comment
			WHERE is_approved > 0
			GROUP BY alias, ptr
			ORDER BY count DESC
			LIMIT %d',
			(int) $limit
		);
		$objects = array();
		foreach ($this->read($sql, array()) as $o) {
			try {
				$col = DMCollectionFactory::getCollection($o['alias']);
				$objects[] = DMObjectFactory::getObject($col, $o['ptr']);
			} catch (DMUnavailableModelException $e) {
				// invalid object; skip it
			}
		}
		return $objects;
	}

	/**************************** RATINGS **********************************/

	/**
	 * @param DMObject obj
	 * @return Indexed array of DMRating objects, or an empty array if there
	 * are none
	 * @since 0.3
	 */
	public final function getAllRatingsForObject(DMObject $obj) {
		$sql = "SELECT value FROM rating WHERE alias = :alias AND ptr = :ptr";
		$params = array(
			':alias' => $obj->getCollection()->getAlias(),
			':ptr' => $obj->getPtr()
		);
		$ratings = array();
		foreach ($this->read($sql, $params) as $r) {
			$ratings[] = new DMRating($r['value'], 100);
		}
		return $ratings;
	}

	/**
	 * @param array collections Array of DMCollections
	 * @param int page
	 * @param int limit
	 * @param int total Returned by reference
	 * @return array Array of DMObjects
	 * @since 1.0
	 */
	public final function getHighestRatedObjects(array $collections, $page,
			$limit, &$total) {
		// extract collection aliases from array of DMCollections
		$aliases = array();
		foreach ($collections as $c) {
			$aliases[] = "'" . addslashes($c->getAlias()) . "'";
		}

		// get the count
		$sql = "SELECT COUNT(id) AS count FROM rating ";
		if (count($aliases)) {
			$sql .= sprintf("WHERE alias IN (%s) ",
					implode(", ", $aliases));
		}
		$result = $this->read($sql, array());
		$total = (int) $result[0]['count'];

		$sql = "SELECT alias, ptr FROM rating ";
		if (count($aliases)) {
			$sql .= sprintf("WHERE alias IN (%s) ",
					implode(", ", $aliases));
		}
		$offset = ($page - 1) * $limit;
		$sql .= sprintf("GROUP BY alias, ptr
			ORDER BY AVG(value) DESC
			LIMIT %d OFFSET %d",
			$limit, $offset);

		$objects = array();
		foreach ($this->read($sql, array()) as $r) {
			try {
				$col = DMCollectionFactory::getCollection($r['alias']);
				$objects[] = DMObjectFactory::getObject($col, $r['ptr']);
			} catch (DMUnavailableModelException $e) {
				// invalid object; skip it
			}
		}
		return $objects;
	}

	/**
	 * @param DMCollection col
	 * @return float
	 */
	public function getMeanNumRatingsPerObjectInCollection(DMCollection $col) {
		$num_objects = $col->getNumObjects();
		if ($num_objects > 0) {
			$num_ratings = $this->getNumRatingsInCollection($col);
			return $num_ratings / $num_objects;
		}
		return (float) 0;
	}

	/**
	 * @param DMCollection col
	 * @return int
	 */
	public final function getMedianNumRatingsPerObjectInCollection(
			DMCollection $col) {
		$sql = "SELECT COUNT(id) AS total
			FROM rating
			WHERE alias = :alias
			GROUP BY ptr
			ORDER BY total";
		$params = array(
			'alias' => $col->getAlias()
		);
		$result = $this->read($sql, $params);

		$counts = array();
		$num_counts = $col->getNumObjects() - count($result);
		for ($i = 0; $i < $num_counts; $i++) {
			$counts[] = 0;
		}
		foreach ($result as $row) {
			$counts[] = $row['total'];
		}
		if ($num_counts) {
			$middle = round($num_counts / 2);
			return ($middle - 1 > 0) ? $counts[$middle - 1] : 0;
		}
		return 0;
	}

	/**
	 * @return int
	 */
	public function getNumObjectsWithRatings() {
		$sql = 'SELECT COUNT(alias) AS count FROM (
				SELECT DISTINCT alias, ptr
				FROM rating
			) AS count';
		$result = $this->read($sql, array());
		return $result[0]['count'];
	}

	/**
	 * @return int
	 * @since 0.3
	 */
	public final function getNumRatings() {
		$sql = "SELECT COUNT(id) AS count FROM rating";
		$r = $this->read($sql, array());
		return $r[0]['count'];
	}

	/**
	 * @param DMObject obj
	 * @return integer
	 * @since 0.3
	 */
	public final function getNumRatingsForObject(DMObject $obj) {
		$sql = "SELECT COUNT(id) AS count FROM rating
			WHERE alias = :alias AND ptr = :ptr";
		$params = array(
			':alias' => $obj->getCollection()->getAlias(),
			':ptr' => $obj->getPtr()
		);
		$result = $this->read($sql, $params);
		return (int) $result[0]['count'];
	}

	/**
	 * @param DMCollection c
	 * @return int
	 */
	public function getNumRatingsInCollection(DMCollection $c) {
		$sql = 'SELECT COUNT(id) AS count FROM rating WHERE alias = :alias';
		$params = array(':alias' => $c->getAlias());
		$r = $this->read($sql, $params);
		return $r[0]['count'];
	}

	/**
	 * @param DMObject obj
	 * @param DMRating rating
	 * @return Boolean
	 * @since 0.3
	 */
	public final function addObjectRating(DMObject $obj, DMRating $rating) {
		$params = array(
			':value' => $rating->getNormalizedValue(),
			':ptr' => $obj->getPtr(),
			':alias' => $obj->getCollection()->getAlias(),
			':posted_at' => date('Y-m-d H:i:s', time())
		);
		if ($rating->getID()) {
			$sql = "INSERT INTO rating(id, value, ptr, alias, posted_at)
				VALUES(:id, :value, :ptr, :alias, :posted_at)";
			$params[':id'] = $rating->getID();
		} else {
			$sql = "INSERT INTO rating(value, ptr, alias, posted_at)
				VALUES(:value, :ptr, :alias, :posted_at)";
		}

		return $this->write($sql, $params);
	}

	/**
	 * @param int limit
	 * @return array of DMObjects, or an empty array if no ratings exist for
	 * any object
	 */
	public function getObjectsWithHighestRatings($limit) {
		$sql = sprintf('SELECT alias, ptr, AVG(value) AS value
			FROM rating
			GROUP BY alias, ptr
			ORDER BY value DESC
			LIMIT %d',
			(int) $limit
		);
		$objects = array();
		foreach ($this->read($sql, array()) as $o) {
			try {
				$col = DMCollectionFactory::getCollection($o['alias']);
				$objects[] = DMObjectFactory::getObject($col, $o['ptr']);
			} catch (DMUnavailableModelException $e) {
				// invalid object; skip it
			}
		}
		return $objects;
	}

	/**
	 * @param int limit
	 * @return array of DMObjects, or an empty array if no tags exist for
	 * any object
	 */
	public function getObjectsWithLowestRatings($limit) {
		$sql = sprintf('SELECT alias, ptr, AVG(value) AS value
			FROM rating
			GROUP BY alias, ptr
			ORDER BY value ASC
			LIMIT %d',
			(int) $limit
		);
		$objects = array();
		foreach ($this->read($sql, array()) as $o) {
			try {
				$col = DMCollectionFactory::getCollection($o['alias']);
				$objects[] = DMObjectFactory::getObject($col, $o['ptr']);
			} catch (DMUnavailableModelException $e) {
				// invalid object; skip it
			}
		}
		return $objects;
	}

	/**
	 * @param int limit
	 * @return array of DMObjects, or an empty array if no tags exist for
	 * any object
	 */
	public function getObjectsWithMostRatings($limit) {
		$sql = sprintf('SELECT COUNT(id) AS count, alias, ptr
			FROM rating
			GROUP BY alias, ptr
			ORDER BY count DESC
			LIMIT %d',
			(int) $limit
		);
		$objects = array();
		foreach ($this->read($sql, array()) as $o) {
			try {
				$col = DMCollectionFactory::getCollection($o['alias']);
				$objects[] = DMObjectFactory::getObject($col, $o['ptr']);
			} catch (DMUnavailableModelException $e) {
				// invalid object; skip it
			}
		}
		return $objects;
	}

	/**
	 * @param array input_objects Array of DMObjects
	 * @param int min
	 * @param int max
	 * @return array Array of DMObjects
	 */
	public final function filterObjectsWithRatingsBetween(array $input_objects,
			$min, $max) {
		$aliases = $ptrs = array();
		foreach ($input_objects as $obj) {
			if (!$obj instanceof DMObject) {
				continue;
			}
			$aliases[] = "'" . $obj->getCollection()->getAlias() . "'";
			$ptrs[] = $obj->getPtr();
		}

		$sql = sprintf("SELECT alias, ptr FROM rating
			WHERE value >= :min AND value <= :max
				AND alias IN (%s) AND ptr IN (%s)",
				implode(",", $aliases), implode(",", $ptrs));
		$params = array(
			':min' => (int) $min,
			':max' => (int) $max
		);
		$objects = array();
		foreach ($this->read($sql, $params) as $r) {
			try {
				$col = DMCollectionFactory::getCollection($r['alias']);
				$objects[] = DMObjectFactory::getObject($col, $r['ptr']);
			} catch (DMUnavailableModelException $e) {
				// invalid object; skip it
			}
		}
		return $objects;
	}

	/**
	 * @param DMObject obj
	 * @return integer
	 * @since 0.3
	 */
	public final function getRatingForObject(DMObject $obj) {
		$sql = "SELECT AVG(value) AS rating FROM rating
			WHERE alias = :alias AND ptr = :ptr";
		$params = array(
			':alias' => $obj->getCollection()->getAlias(),
			':ptr' => $obj->getPtr()
		);
		$r = $this->read($sql, $params);
		return $r[0]['rating'];
	}

	/****************************** TAGS *********************************/

	/**
	 * @param DMTagQuery query
	 * @param bool count Whether to return the result count, or the result
	 * @return Array of DMTag objects, or associative array of tag count =>
	 * value pairs, or int, depending on the tag query
	 */
	function getTagsForQueryAsCounts(DMTagQuery $query, $count) {
		$select = "SELECT *, COUNT(id) AS count ";
		if ($count) {
			$select = "SELECT COUNT(id) AS count ";
		}
		$from = "FROM tag ";

		$params = $where = $tmp = array();
		$i = 0;

		if (count($query->getObjects())) {
			foreach ($query->getObjects() as $obj) { // object
				$tmp[] = sprintf("(alias = :alias%d AND ptr = :ptr%d)", $i, $i);
				$params[':alias' . $i] = $obj->getCollection()->getAlias();
				$params[':ptr' . $i] = $obj->getPtr();
				$i++;
			}
		} else { // collections
			$skip_collections = false;
			foreach ($query->getCollections() as $col) {
				if ($col->getAlias() == "/dmdefault") {
					$skip_collections = true;
					break;
				}
			}
			if (!$skip_collections) {
				foreach ($query->getCollections() as $col) {
					$tmp[] = sprintf("alias = :alias%d", $i);
					$params[':alias' . $i] = $col->getAlias();
					$i++;
				}
			}
		}
		if (count($tmp)) {
			$where[] = implode(" OR ", $tmp);
		}

		// approved
		if ($query->getApproved() == -1) {
			$where[] = "is_approved < 1";
		} else if ($query->getApproved() == 1) {
			$where[] = "is_approved > 0";
		}
		
		// terms
		$i = 0;
		$tmp = array();
		foreach ($query->getPredicates() as $pred) {
			$tmp[] = sprintf("value LIKE :term%d", $i);
			$params[':term' . $i] = "%" . $pred->getString() . "%";
			$i++;
		}
		if (count($tmp)) {
			$where[] = implode(" AND ", $tmp);
		}

		// sort
		$group = $order = $limit = $offset = "";
		if (!$count) {
			$group = " GROUP BY value ";
		
			// order
			$order = "";
			if ($query->isSortingByFrequency()) {
				foreach ($query->getSortFields() as $field => $direction) {
					$order = sprintf(" ORDER BY %s %s ", $field, $direction);
				}
			} else {
				$order = ($this instanceof DMSQLiteDataStore)
					? " ORDER BY RANDOM()" : "ORDER BY RAND()";
			}
			// limit/offset
			$limit = sprintf(" LIMIT %d ", $query->getNumResultsPerPage());
			$offset = sprintf(" OFFSET %d ", $query->getStart());
		}

		$sql = $select . $from . "WHERE " . implode(" AND ", $where) . $group
				. $order . $limit . $offset;

		$tags = array();

		$result = $this->read($sql, $params);

		if ($count) {
			return $result[0]['count'];
		} else {
			foreach ($result as $t) {
				$tags[$t['value']] = $t['count'];
			}
		}
		return $tags;
	}

	/**
	 * @param DMTagQuery query
	 * @param bool count Whether to return the result count, or the result
	 * @return Array of DMTag objects, or associative array of tag count =>
	 * value pairs, or int, depending on the tag query
	 */
	function getTagsForQueryAsObjects(DMTagQuery $query, $count) {
		$select = "SELECT * ";
		if ($count) {
			$select = "SELECT COUNT(id) AS count ";
		}
		$from = "FROM tag ";

		$params = $where = $tmp = array();
		$i = 0;

		if (count($query->getObjects())) {
			foreach ($query->getObjects() as $obj) { // object
				$tmp[] = sprintf("(alias = :alias%d AND ptr = :ptr%d)", $i, $i);
				$params[':alias' . $i] = $obj->getCollection()->getAlias();
				$params[':ptr' . $i] = $obj->getPtr();
				$i++;
			}
		} else { // collections
			$skip_collections = false;
			foreach ($query->getCollections() as $col) {
				if ($col->getAlias() == "/dmdefault") {
					$skip_collections = true;
					break;
				}
			}
			if (!$skip_collections) {
				foreach ($query->getCollections() as $col) {
					$tmp[] = sprintf("alias = :alias%d", $i);
					$params[':alias' . $i] = $col->getAlias();
					$i++;
				}
			}
		}
		if (count($tmp)) {
			$where[] = implode(" OR ", $tmp);
		}

		// approved
		if ($query->getApproved() == -1) {
			$where[] = "is_approved < 1";
		} else if ($query->getApproved() == 1) {
			$where[] = "is_approved > 0";
		}
		
		// terms
		$i = 0;
		$tmp = array();
		foreach ($query->getPredicates() as $pred) {
			$tmp[] = sprintf("value LIKE :term%d", $i);
			$params[':term' . $i] = "%" . $pred->getString() . "%";
			$i++;
		}
		if (count($tmp)) {
			$where[] = implode(" AND ", $tmp);
		}

		// sort
		$order = $limit = $offset = "";
		if (!$count) {
			// order
			$order = "";
			if ($query->isSortingByFrequency()) {
				// order
				if (count($query->getSortFields())) {
					$order = " ORDER BY";
					$orders = array();
					foreach ($query->getSortFields() as $field => $direction) {
						$orders[] = sprintf(" %s %s ", $field, $direction);
					}
					$order .= implode(", ", $orders);
				}
			} else {
				$order = ($this instanceof DMSQLiteDataStore)
					? " ORDER BY RANDOM()" : " ORDER BY RAND()";
			}
			// limit/offset
			$limit = sprintf(" LIMIT %d ", $query->getNumResultsPerPage());
			$offset = sprintf(" OFFSET %d ", $query->getStart());
		}

		$sql = $select . $from . "WHERE " . implode(" AND ", $where)
				. $order . $limit . $offset;

		$tags = array();

		$result = $this->read($sql, $params);

		if ($count) {
			return $result[0]['count'];
		} else {
			foreach ($result as $t) {
				try {
					$tags[] = $this->loadTag($t['id']);
				} catch (DMUnavailableModelException $e) {
					// tag is associated with an invalid object; skip it
				}
			}
		}
		return $tags;
	}

	/**
	 * Deletes all tags with the given value.
	 *
	 * @param string value
	 * @return int The number of tags deleted
	 */
	public function deleteAllTagsWithValue($value) {
		$sql = "DELETE FROM tag WHERE UPPER(value) = UPPER(:value)";
		$params = array(
			'value' => $value
		);
		return $this->write($sql, $params);
	}

	/**
	 * @param DMObject obj
	 * @param DMTag tag
	 * @return Boolean
	 */
	public final function addObjectTag(DMObject $obj, DMTag $tag) {
		// check for duplicates, return true without saving if found
		$sql = "SELECT COUNT(id) AS count FROM tag
			WHERE value = :value AND ptr = :ptr AND alias = :alias";
		$params = array(
			':value' => $tag->getValue(),
			':ptr' => $obj->getPtr(),
			':alias' => $obj->getCollection()->getAlias()
		);
		$result = $this->read($sql, $params);
		if ($result[0]['count']) {
			return true;
		}
		// apparently no duplicates, so insert
		$params = array(
			':value' => $tag->getValue(),
			':ptr' => $obj->getPtr(),
			':alias' => $obj->getCollection()->getAlias(),
			':is_approved' => (int) $tag->isApproved(),
			':posted_at' => date('Y-m-d H:i:s', time())
		);
		if ($tag->getID()) {
			$sql = "INSERT INTO tag(id, value, ptr, alias, is_approved,
				posted_at)
				VALUES(:id, :value, :ptr, :alias, :is_approved, :posted_at)";
			$params[':id'] = $tag->getID();
		} else {
			$sql = "INSERT INTO tag(value, ptr, alias, is_approved, posted_at)
				VALUES(:value, :ptr, :alias, :is_approved, :posted_at)";
		}

		$this->write($sql, $params);

		// get last-inserted id in a database-independent way
		$sql = "SELECT MAX(id) AS id FROM tag";
		$result = $this->read($sql, array());
		$tag->setID($result[0]['id']);
		return true;
	}

	/**
	 * @param DMCollection col
	 * @return float
	 */
	public function getMeanNumTagsPerObjectInCollection(DMCollection $col) {
		$num_objects = $col->getNumObjects();
		if ($num_objects > 0) {
			$tq = new DMTagQuery($this);
			$tq->setApproved(1);
			$tq->addCollection($col);
			$tq->getSearchResults();
			return $tq->getNumResults() / $num_objects;
		}
		return (float) 0;
	}

	/**
	 * @param DMCollection col
	 * @return int
	 */
	public final function getMedianNumTagsPerObjectInCollection(
			DMCollection $col) {
		$sql = "SELECT COUNT(id) AS total
			FROM tag
			WHERE alias = :alias
			GROUP BY ptr
			ORDER BY total";
		$params = array(
			'alias' => $col->getAlias()
		);
		$result = $this->read($sql, $params);

		$counts = array();
		$num_counts = $col->getNumObjects() - count($result);
		for ($i = 0; $i < $num_counts; $i++) {
			$counts[] = 0;
		}
		foreach ($result as $row) {
			$counts[] = $row['total'];
		}
		if ($num_counts) {
			$middle = round($num_counts / 2);
			return ($middle - 1 > 0) ? $counts[$middle - 1] : 0;
		}
		return 0;
	}

	/**
	 * @return int
	 */
	public function getNumObjectsWithApprovedTags() {
		$sql = 'SELECT COUNT(alias) AS count FROM (
				SELECT DISTINCT alias, ptr
				FROM tag
				WHERE is_approved > 0
			) AS count';
		$result = $this->read($sql, array());
		return $result[0]['count'];
	}

	/**
	 * @param int limit
	 * @return array of DMObjects, or an empty array if no tags exist for
	 * any object
	 */
	public function getObjectsWithMostTags($limit) {
		$sql = sprintf('SELECT COUNT(id) AS count, alias, ptr
			FROM tag
			WHERE is_approved > 0
			GROUP BY alias, ptr
			ORDER BY count DESC
			LIMIT %d',
			(int) $limit
		);
		$objects = array();
		foreach ($this->read($sql, array()) as $o) {
			$col = DMCollectionFactory::getCollection($o['alias']);
			$objects[] = DMObjectFactory::getObject($col, $o['ptr']);
		}
		return $objects;
	}

	/**
	 * @param DMTag tag
	 * @return Boolean
	 */
	public function deleteTag(DMTag $tag) {
		$sql = 'DELETE FROM tag WHERE id = :id';
		$params = array(':id' => $tag->getID());
		return $this->write($sql, $params);
	}

	/**
	 * @param int id The ID of the tag to load
	 * @return DMTag
	 * @throws DMUnavailableModelException
	 */
	public final function loadTag($id) {
		$sql = 'SELECT * FROM tag WHERE id = :id';
		$params = array(':id' => $id);
		$r = $this->read($sql, $params);
		if (sizeof($r) > 0) {
			$tag = new DMTag();
			$tag->setID($id);
			$tag->setValue($r[0]['value']);
			$tag->setTimestamp(new DMDateTime($r[0]['posted_at']));
			$tag->setApproved((bool) $r[0]['is_approved']);
			$collection = DMCollectionFactory::getCollection($r[0]['alias']);
			if ($collection) {
				$object = DMObjectFactory::getObject($collection, $r[0]['ptr']);
				$tag->setObject($object);
				return $tag;
			}
			return null;
		} else {
			throw new DMUnavailableModelException(
				DMLocalizedString::getString('INVALID_TAG'));
		}
	}

	/**
	 * @param DMTag tag
	 * @return Boolean
	 * @throws DMPDOException
	 */
	public final function saveTag(DMTag $tag) {
		$sql = "UPDATE tag SET value = :value, is_approved = :is_approved
			WHERE id = :id";
		$params = array(
			':value' => $tag->getValue(),
			':is_approved' => (int) $tag->isApproved(),
			':id' => $tag->getID()
		);
		return $this->write($sql, $params);
	}

}
